package com.dzzw.Dao;

import com.dzzw.Model.User;
import com.dzzw.utils.DButils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//群众用户
public class UserDao {
    private final Connection con = DButils.getCon();
    //用户登录;
    public static List<User> UserLogin(String user_actnumber,String user_password) {
        List<User> list = new ArrayList<>();
        String sql = "select * from t_user where user_actnumber=? and user_password=?";
        try {
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, user_actnumber);
            preparedStatement.setString(2, user_password);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                User user  = new User();
                user.setUser_id(rs.getInt("user_id"));
                user.setUser_name(rs.getString("user_name"));
                user.setUser_actnumber(rs.getString("user_actnumber"));
                user.setUser_password(rs.getString("user_password"));
                user.setUser_address(rs.getString("user_address"));
                user.setUser_age(rs.getInt("user_age"));
                user.setUser_cardnumber(rs.getString("user_cardnumber"));
                user.setUser_email(rs.getString("user_email"));
                user.setUser_nickname(rs.getString("user_nickname"));
                user.setUser_phone(rs.getString("user_phone"));
                user.setUser_sex(rs.getString("user_sex"));
                user.setUser_status(rs.getInt("user_status"));
                list.add(user);
            }
            rs.close();
            preparedStatement.close();
            con.close();
        } catch(Exception e){
            System.out.println("数据查询失败！");
            e.printStackTrace();
        }
        return list;
    }

    //用户注册;
    public void UserRegister(String user_password,String user_actnumber,String user_nickname) {
        String sql = "insert into t_user(user_nickname,user_password,user_actnumber,user_status) values(?,?,?,?)";
        try {
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, user_nickname);
            preparedStatement.setString(2, user_password);
            preparedStatement.setString(3, user_actnumber);
            preparedStatement.setInt(4,0);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("用户注册失败！");
            e.printStackTrace();
        }
    }

    //用户实名认证;
    public void UserAttestation(String user_name,String user_cardnumber,String user_email,String user_sex,String user_phone,String user_address,Integer user_age,Integer user_id) {
        String sql = "update t_user t set t.user_cardnumber=?,t.user_email=?,t.user_sex=?,t.user_phone=?,t.user_address=?,t.user_name=?,t.user_age=?,t.user_status = 1 where t.user_id = ?";
        try {
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, user_cardnumber);
            preparedStatement.setString(2, user_email);
            preparedStatement.setString(3, user_sex);
            preparedStatement.setString(4, user_phone);
            preparedStatement.setString(5, user_address);
            preparedStatement.setString(6,user_name);
            preparedStatement.setInt(7,user_age);
            preparedStatement.setInt(8,user_id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("用户实名认证失败！");

            e.printStackTrace();
        }
    }

    //用户注销;
    public void UserDelete(Integer user_id){
        String sql = "Delete FROM t_user WHERE user_id = ?";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,user_id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e){
            System.out.println("数据注销失败！");

            e.printStackTrace();
        }
    }
    
    //用户信息修改;
    public void UserModify(User user,Integer user_id) {
        String sql = "UPDATE t_user SET t_user.user_nickname=?,t_user.user_actnumber=?,t_user.user_password=?,t_user.user_name=?,t_user.user_cardnumber=?,t_user.user_sex=?,t_user.user_age=?,t_user.user_email=?,t_user.user_phone=?,t_user.user_address=?,t_user.user_status=? WHERE t_user.user_id=?";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1,user.getUser_nickname());
            preparedStatement.setString(2,user.getUser_actnumber());
            preparedStatement.setString(3,user.getUser_password());
            preparedStatement.setString(4,user.getUser_name());
            preparedStatement.setString(5,user.getUser_cardnumber());
            preparedStatement.setString(6,user.getUser_sex());
            preparedStatement.setInt(7,user.getUser_age());
            preparedStatement.setString(8,user.getUser_email());
            preparedStatement.setString(9,user.getUser_phone());
            preparedStatement.setString(10,user.getUser_address());
            preparedStatement.setInt(11,user.getUser_status());
            preparedStatement.setInt(12,user_id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        }catch(Exception e){
            System.out.println("数据修改失败！");

            e.printStackTrace();
        }
    }
    
    //所有用户查询;
    public static List<User> SelectAllUser(){
        List<User> list = new ArrayList<>();
        String sql = "select * from t_user";
        try {
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                User user = new User();
                user.setUser_id(resultSet.getInt(1));
                user.setUser_nickname(resultSet.getString(2));
                user.setUser_actnumber(resultSet.getString(3));
                user.setUser_password(resultSet.getString(4));
                user.setUser_name(resultSet.getString(5));
                user.setUser_cardnumber(resultSet.getString(6));
                user.setUser_sex(resultSet.getString(7));
                user.setUser_age(resultSet.getInt(8));
                user.setUser_phone(resultSet.getString(9));
                user.setUser_email(resultSet.getString(10));
                user.setUser_address(resultSet.getString(11));
                user.setUser_status(resultSet.getInt(12));
                list.add(user);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch (Exception e){
            System.out.println("查询所有数据失败！");
            e.printStackTrace();
        }
        return list;
    }
    
    //通过User_id查询单个用户数据;
    public static List<User> SelectByIdUser(Integer user_id){
        List<User> list = new ArrayList<>();
        String sql = "select * from t_user where user_id=?";

        try {
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,user_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                User user = new User();
                user.setUser_id(resultSet.getInt(1));
                user.setUser_nickname(resultSet.getString(2));
                user.setUser_actnumber(resultSet.getString(3));
                user.setUser_password(resultSet.getString(4));
                user.setUser_name(resultSet.getString(5));
                user.setUser_cardnumber(resultSet.getString(6));
                user.setUser_sex(resultSet.getString(7));
                user.setUser_age(resultSet.getInt(8));
                user.setUser_phone(resultSet.getString(9));
                user.setUser_email(resultSet.getString(10));
                user.setUser_address(resultSet.getString(11));
                user.setUser_status(resultSet.getInt(12));
                list.add(user);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch (Exception e){
            System.out.println("查询所有数据失败！");
            e.printStackTrace();
        }
        return list;
    }

    //查询已实名的用户信息

    //查询未实名的用户信息

    //数据批量注销;

    //分页查询;
    public List<User> FindAllUser(int pageIndex, int pageSize) {
        String sql = "SELECT * FROM t_user ORDER BY user_id ASC limit ?,?";
        User user = new User();
        List<User> list = new ArrayList<>();
        try {
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1, (pageIndex - 1) * pageSize);
            preparedStatement.setInt(2, pageSize);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                user.setUser_id(resultSet.getInt(1));
                user.setUser_nickname(resultSet.getString(2));
                user.setUser_actnumber(resultSet.getString(3));
                user.setUser_password(resultSet.getString(4));
                user.setUser_name(resultSet.getString(5));
                user.setUser_cardnumber(resultSet.getString(6));
                user.setUser_sex(resultSet.getString(7));
                user.setUser_age(resultSet.getInt(8));
                user.setUser_phone(resultSet.getString(9));
                user.setUser_email(resultSet.getString(10));
                user.setUser_address(resultSet.getString(11));
                user.setUser_status(resultSet.getInt(12));
                list.add(user);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //总页数;
    public int count() {
        int result = 0;
        String sql = "SELECT count(1) FROM t_user";
        try {
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                result = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}
